﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class AgendaDataAccess
    {
        public static bool InsertNewAppointment(Appointment appointment)
        {
            if (appointment != null)
            {
                string stringConnection = "Insert into Appointment(DateAppointment,TimeAppointment,StateAppointment,NoteBook_IdNoteBook,Patient_CiPatient)values('" + appointment.DateAppointment + "','" + appointment.TimeAppointment + "','" + appointment.StateAppointment + "',1,'" + appointment.CIPatient + "') ";
                return ExecuteConsult(stringConnection);
            }
            else 
            {
                return false;
            }
        }

        private static bool ExecuteConsult(string stringConnection)
        {
            using (DBConnection connection = new DBConnection())
            {
                if (connection.CommandExecuteConsult(stringConnection))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        public static DataTable LoadDataGridViewHorary(string dateTimeOrange,string ciUser)
        {
            string stringConnection = "Select a.*, p.FirstName, p.LastName, p.CellPhone, p.Phone ,p.Email  From Agenda a, Patient p where a.CiPatient=p.Ci and a.DateAgenda='" + dateTimeOrange + "' and a.CiDoctor='"+ciUser+"'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }

        }

        public static DataTable LoadDataAppointmentsForToday()
        {            
            string stringConnection = "select a.TimeAgenda, p.FirstName, p.Picture, p.Ci from Agenda a, Patient p where p.Ci=a.CiPatient and CONVERT(varchar(10), a.DateAgenda, 103)=CONVERT(varchar(10), GETDATE(), 103) ";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }
        //public static DataTable LoadDataPatientsNextAppointment(int ciPatient)
        //{
        //    string stringConnection = "select a.DateAgenda, a.TimeAgenda, m.FirstName from Manager m, Patient p, Agenda a where a.CiDoctor=m.Ci and p.ci='" + ciPatient + "' and a.ciPatient=p.ci and CONVERT(varchar(10), a.DateAgenda, 103)>GETDATE()";
        //    using (DBConnection connection = new DBConnection())
        //    {
        //        return connection.ConsultationSelectDataTable(stringConnection);
        //    }
        //}

        public static bool ModifyAppointment(Appointment appointment, string idAppointment)
        {
            if (appointment != null)
            {
                string stringConnection = "update Appointment set DateAppointment= '" + appointment.DateAppointment + "',TimeAppointment='" + appointment.TimeAppointment + "',StateAppointment='" + appointment.StateAppointment + "' where Patient_CiPatient='" + idAppointment + "'";
                return ExecuteConsult(stringConnection);
            }
            else
            {
                return false;
            }
        }

        public static DataTable LoadAgenda()
        {
            string stringConnection = "select NameNoteBook from NoteBook ";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static bool NewAgenda(string nameAgenda)
        {
            string stringConnection = "Insert into NoteBook(NameNoteBook,StateNoteBook)values('" + nameAgenda + "','Activo') ";
            return ExecuteConsult(stringConnection);
        }

        public static bool ModifyAgenda(string newName, string oldName)
        {
            string stringConnection = "Update NoteBook set NameNoteBook='"+newName+"'where NameNoteBook='"+oldName+"' ";
            return ExecuteConsult(stringConnection);
        }

        public static DataTable SearchAgenda(string nameAgenda)
        {
            string stringConnection = "select NameNoteBook from NoteBook where NameNoteBook='"+nameAgenda+"' ";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }
    }
}
